﻿using NB.MGJ.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NB.MGJ.Common.Helper;
using System.Data;
using MySql.Data.MySqlClient;

namespace NB.MGJ.Service
{
    public partial class ManagerService
    {
        #region 用户

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public PagedList<V_User> GetUserInfoService(ref PagerModel pager, int userid, int? enterpriseId)
        {
            string sql = " ( SELECT  u.ID, u.ViewDel, u.ViewFlag, u.EnterpriseId, u.RealName, u.LoginName, Modile, u.Remark,u.ChangeLoginID,u.ChangeLoginName,u.ChangeDate, u.City,EnterpriseName  ,LEFT(GROUP_CONCAT(RoleName,\",\"),char_length(GROUP_CONCAT(RoleName,\",\"))-1)as RoleNames  FROM   v_user u LEFT JOIN sys_roleuserInfo ur ON ur.uid = u.id LEFT JOIN sys_role r ON ur.rid = r.id GROUP BY uid) u  ";
            pager.TblName = sql;
            pager.FldName = " *   ";
            if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == "")
                pager.FldSort = "  ChangeDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_User> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_User>("Common_PageList", parameters);
            return new PagedList<V_User>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        public PagedList<V_User> GetUserInfoService(ref PagerModel pager, string whereStr)
        {

            pager.TblName = " sys_user u left join (select *,LEFT(GROUP_CONCAT(RoleName),char_length(GROUP_CONCAT(RoleName,\",\"))-1)as RoleNames from ( select u.* ,r.RoleName,ur.uid from  v_user u LEFT JOIN sys_roleuserInfo ur ON ur.uid = u.id LEFT JOIN sys_role r ON ur.rid = r.id " +
" ) u where " + whereStr + " GROUP BY uid) w on w.id=u.id ";
            pager.FldName = " w.RoleNames,u.*,EnterpriseName  ";
            if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == "")
                pager.FldSort = "  u.ChangeDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_User> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_User>("Common_PageList", parameters);
            return new PagedList<V_User>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="_user"></param>
        /// <returns></returns>
        public int AddUserInfoService(Sys_User user)
        {
            return _baseRepository.InsertReturnID<Sys_User>(user);
        }
        /// <summary>
        /// 关联角色
        /// </summary>
        /// <param name="_user"></param>
        /// <returns></returns>
        public int RelationRole(string sql)
        {
            return _baseRepository.ExecuteSql<int>(sql);
        }
        
        #region 导出查询
        public IEnumerable<Sys_User> GetAllUser(string SeachWhere, int id)
        {
            var rs = "SELECT * FROM Sys_User where ViewDel=0 ";
            if (!string.IsNullOrWhiteSpace(SeachWhere))
            {
                rs += " and id In(" + SeachWhere + ")"; 
            }

            return _baseRepository.ExecuteSqlCollection<Sys_User>(rs);
        }
        #endregion


        public int EditUserInfoService(Sys_User user)
        {
            return _baseRepository.Update<Sys_User>(user);
        }
       /// <summary>
       /// 
       /// </summary>
       /// <param name="id">用户id</param>
       /// <param name="RoleId">角色集合</param>
       /// <returns></returns>
        public int EditUserRoleService(int id, string[] RoleId)
        {
            string str = string.Format("delete from Sys_RoleUserInfo where Uid={0} ", id);
            _baseRepository.ExecuteSql<int>(str);
            string sql=string.Empty;
            int sum = 0;
            for (int i = 0; i < RoleId.Length; i++)
            {
                sql = string.Format("insert into Sys_RoleUserInfo(RID,UID) values({0},{1})", RoleId[i], id);
                sum += _baseRepository.ExecuteSql<int>(sql);
            }
            return sum;
        }

        public Sys_User GetUserInfoById(int id)
        {
            string sql = "select ID, EnterpriseId, RealName, LoginName, Pwd, Modile, Remark, Province ,City,Distributor,Type  from Sys_User where ID=" + id;
            return _baseRepository.ExecuteSqlCollection<Sys_User>(sql).ToList().FirstOrDefault();
        }
        public AppUserModel GetAppUserInfoById(int id)
        {
            string sql = string.Format(@"SELECT	s.ID,	UserImg,	EnterpriseId,	RealName,	LoginName,	Modile,	(SELECT		GROUP_CONCAT(RoleName) FROM	sys_role WHERE ID = (	SELECT GROUP_CONCAT(Rid) FROM	sys_roleuserinfo WHERE uid ={0})	) AS RoleName,
	EnterpriseName,	(	SELECT `name` FROM sys_region 	WHERE codeID = provinceid	) AS provincename,
	(		SELECT			`name`		FROM			sys_region		WHERE			codeID = e.City	) AS City,
	(		SELECT			`name`		FROM			sys_region		WHERE			codeID = Region	) AS Region
FROM	sys_user s INNER JOIN organization_enterprise e ON s.EnterpriseId = e.ID WHERE	s.id ={1}", id, id);
            return _baseRepository.ExecuteSqlCollection<AppUserModel>(sql).ToList().FirstOrDefault();
        }
        public AppUserModel GetAppUserInfo(int id)
        {
            string sql = "SELECT  Modile,RealName,REPLACE(UserImg,'http://','https://') as UserImg FROM sys_user where ID=" + id;
            return _baseRepository.ExecuteSqlCollection<AppUserModel>(sql).ToList().FirstOrDefault();
        }

        public IEnumerable<Sys_RoleUserInfo> GetRoleUserInfoByUserId(int id)
        {
            string sql = "select ID, Rid, Uid  from Sys_RoleUserInfo where Uid=" + id;
            return _baseRepository.ExecuteSqlCollection<Sys_RoleUserInfo>(sql);
        }
        public Sys_User GetCodeModelId(string RealName)
        {
            string sql = "SELECT RealName FROM sys_user WHERE RealName=" + RealName;
            var result = _baseRepository.ExecuteSqlCollection<Sys_User>(sql).FirstOrDefault();
            return result;
        }
        public bool GetByUserName(string LoginName)
        {
            int dd = Convert.ToInt32(_baseRepository.GetSingle("SELECT COUNT(*) FROM sys_user WHERE LoginName='" + LoginName + "'"));
            bool result = dd > 0 ? false : true;
            return result;
        }
        public int leavel(int eid)
        {
            string sqls = "SELECT max(Level) from antifleeing_distributorlevel where ViewDel=0 and EnterpriseId="+eid;
            int res=_baseRepository.ExecuteText<int>(sqls);

            return res;
        }
        public bool GetDealerLevelName(string Name, int EnterpriseId)
        {
            string sql = string.Format("SELECT	COUNT(*) FROM	antifleeing_distributorlevel WHERE	ViewDel = 0 AND EnterpriseId = '{0}' AND `LevelName` ='{1}'", EnterpriseId, Name);
            int dd = Convert.ToInt32(_baseRepository.GetSingle(sql));
            bool result = dd > 0 ? false : true;
            return result;
        }
        public bool GetDealerLevel(string Name, int EnterpriseId)
        {
            string sql = string.Format("SELECT	COUNT(*) FROM	antifleeing_distributorlevel WHERE	ViewDel = 0 AND EnterpriseId = '{0}' AND `Level` ='{1}'", EnterpriseId, Name);
            int dd = Convert.ToInt32(_baseRepository.GetSingle(sql));
            bool result = dd > 0 ? false : true;
            return result;
        }
        public int OrganAddUserInfoService(L.EntityModel.Sys_User user)
        {
            return baseRepository2.Insert<L.EntityModel.Sys_User>(user);
        }

        public int DelUserService(Sys_User user)
        {
            return _baseRepository.Update<Sys_User>(user);
        }


        /// <summary>
        /// 获取当前机构的所有用户
        /// </summary>
        public List<Sys_User> GetSysUserListByEID(int EID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.*,b.Rid from sys_user a LEFT JOIN sys_roleuserinfo b on a.ID=b.Uid where EnterpriseId=" + EID + " and rid=13");
            var sql = sb.ToString();
            var result = _baseRepository.ExecuteSqlCollection<Sys_User>(sql).ToList();
            return result;
        }

        public List<Sys_User> GetNodeAddUserListByEID(int EID)
        {
            StringBuilder sb = new StringBuilder();
            //sb.Append("select * from sys_user where EnterpriseId=" + EID + " and ViewDel=0 " );

            sb.Append("select a.*,b.Rid from sys_user a LEFT JOIN sys_roleuserinfo b on a.ID=b.Uid where EnterpriseId=" + EID + " and rid=13");
            var result = _baseRepository.ExecuteSqlCollection<Sys_User>(sb.ToString()).ToList();



            return result;
        }

        /// <summary>
        /// 获取机构的默认管理员用户
        /// </summary>
        /// <param name="Eid"></param>
        /// <returns></returns>
        public Sys_User UserByEid(string Code) 
        {
            string sql = "select  * from sys_user where LoginName='" + Code+"'";
           return _baseRepository.ExecuteText<Sys_User>(sql);
        }

        /// <summary>
        /// 获取某机构某角色下的用户列表
        /// </summary>
        /// <param name="EID"></param>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<Sys_User> GetUserListByEIDAndRoleId(int EID, int roleId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from sys_user a  left join sys_roleuserinfo b on a.ID=b.Uid  where EnterpriseId=" + EID + " and ViewDel=0 and Rid=" + roleId);
            var result = _baseRepository.ExecuteSqlCollection<Sys_User>(sb.ToString()).ToList();
            return result;
        }


        public sys_MenuRoleInfo GetSys_MenuRoleInfoByRoleIdAndMenuId(string roleId, int MenuId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from sys_menuroleinfo where Rid in (" + roleId + ") and MenuID=" + MenuId + "");
            var result = _baseRepository.ExecuteSqlCollection<sys_MenuRoleInfo>(sb.ToString()).FirstOrDefault();
            return result;
        }

        //根据用户名和手机号查询
        public Sys_User GetUserInfoByName(string loginName)
        {
            string sql = "select ID, EnterpriseId, RealName, LoginName, Pwd, Modile, Remark, Province ,City,Distributor,Type  from Sys_User where LoginName='{0}'";
            sql = string.Format(sql, loginName);
            return _baseRepository.ExecuteSqlCollection<Sys_User>(sql).ToList().FirstOrDefault();
        }

        public int GetEnterpriseDefaultUserId(Organization_Enterprise model)
        {
            string sql = "select * from sys_user where LoginName=" + model.Code;
            var result = _baseRepository.ExecuteSqlCollection<Sys_User>(sql).FirstOrDefault();
            return result.ID;
        }


        #endregion

        #region 仓库管理 昝小链

        //Pass 1
        public AntiFleeing_Warehouse GeWarehouseModelId(int id)
        {
            string sql = "select a.ID,EnterpriseName, EnterpriseId, SaleAreaId, WarehouseName, a.Address, Contacts, ContactTel, a.Remark, a.ViewDel, a.ChangeLoginID, a.ChangeLoginName, a.ChangeDate from AntiFleeing_Warehouse a left join Organization_Enterprise e on a.EnterpriseId=e.ID where a.ID=" + id;
            return _baseRepository.ExecuteText<AntiFleeing_Warehouse>(sql);
        }

        /// <summary>
        /// 分页显示
        /// </summary>
        /// <param name="page"></param>
        /// <param name="Psearch"></param>
        /// <returns></returns>
        public PagedList<AntiFleeing_Warehouse> GetWareHouseService(ref PagerModel page, string Wsearch)
        {
            page.FldName = @"ID,  EnterpriseName,WarehouseName,Address, Contacts, ContactTel,Remark,ChangeDate ";
            page.TblName = @"(select a.ID,EnterpriseName, EnterpriseId, SaleAreaId, WarehouseName, a.Address, Contacts, ContactTel, a.Remark, a.ViewDel, a.ChangeLoginID, a.ChangeLoginName, a.ChangeDate from AntiFleeing_Warehouse a left join Organization_Enterprise e on a.EnterpriseId=e.ID) z";
            string StrCondition = " ViewDel=0 ";

            StrCondition += page.StrCondition;

            page.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(page.FldSort) || page.FldSort == "")
                page.FldSort = "ID  desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref page);
            IEnumerable<AntiFleeing_Warehouse> resultList = _baseRepository.ExecuteStoredProcedureCollection<AntiFleeing_Warehouse>("Common_PageList", parameters);
            return new PagedList<AntiFleeing_Warehouse>(resultList, page.Page, page.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        //微信小程序仓库下拉框
        public IEnumerable<SelectItem> GetwarehouseList()
        {
            string sql = " select ID,WarehouseName from AntiFleeing_Warehouse where WarehouseName is not null";
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.WarehouseName
                };
            });
        }
        //微信小程序仓位下拉框
        public IEnumerable<SelectItem> GetLocationList()
        {
            string sql = " select Id,Location from AntiFleeing_IOWarehouse where Location is not null";
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_IOWarehouse>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.Id,
                    Name = p.Location
                };
            });
        }

        /// <summary>
        /// 新增仓库
        /// </summary>
        /// <param name="afwh"></param>
        /// <returns></returns>
        public int AddWareHouseService(L.EntityModel.AntiFleeing_Warehouse awh)
        {
            return baseRepository2.Insert<L.EntityModel.AntiFleeing_Warehouse>(awh);
        }
        /// <summary>
        /// 修改仓库
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int EditWareService(AntiFleeing_Warehouse model)
        {

            return baseRepository2.Update<L.EntityModel.AntiFleeing_Warehouse>(p => p.ID == model.ID, p =>
            {
                p.SaleAreaId = model.SaleAreaId;
                p.WarehouseName = model.WarehouseName;
                p.Address = model.Address;
                p.Contacts = model.Contacts;
                p.ContactTel = model.ContactTel;
                p.Remark = model.Remark;
                p.ChangeLoginID = model.ChangeLoginID;
                p.ChangeLoginName = model.ChangeLoginName;
                p.ChangeDate = model.ChangeDate;
               
               
            });
            #region MyRegion
            // string sql = string.Format("update AntiFleeing_Warehouse set `SaleAreaId`='{0}',`WarehouseName`='{1}',`Address`='{2}',`Contacts`='{3}',`ContactTel`='{4}',`Remark`='{5}',`ChangeLoginID`='{6}',`ChangeLoginName`='{7}',`ChangeDate`='{8}' where ID={9}",         model.SaleAreaId,model.WarehouseName,model.Address,model.Contacts,model.ContactTel,model.Remark,model.ChangeLoginID,model.ChangeLoginName,model.ChangeDate,model.ID);
            //return _baseRepository.ExecuteSql<int>(sql);
            //return _baseRepository.Update<AntiFleeing_Warehouse>(model); 
            #endregion
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
      

        public int DelWareService(int Id)
        {
            return baseRepository2.Update<L.EntityModel.AntiFleeing_Warehouse>(p => p.ID == Id, p =>
            {
                p.ViewDel = 1;
            });
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="qsearch"></param>
        /// <returns></returns>
        public IEnumerable<AntiFleeing_Warehouse> GetAllWarehouse(string Wsearch)
        {
            var resule = "select a.ID,EnterpriseName, EnterpriseId, SaleAreaId, WarehouseName, a.Address, Contacts, ContactTel, a.Remark, a.ViewDel, a.ChangeLoginID, a.ChangeLoginName, a.ChangeDate from AntiFleeing_Warehouse a left join Organization_Enterprise e on a.EnterpriseId=e.ID";
            if (!string.IsNullOrWhiteSpace(Wsearch))
            {
                resule += " where a.ID in (" + Wsearch + ")";
            }
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(resule);
        }
        /// <summary>
        /// 下拉框绑定
        /// </summary>
        /// <returns></returns>


        public List<Organization_Enterprise> GetAntiService(int EnterpriseId)
        {
            string sql = "";
            if (EnterpriseId == 1)
            {

                sql = "select ID, Code, EnterpriseName, OrganizationForm, OrganizationId, SuperiorsOrganization, ProvinceId, City, Region, Address, StartTime, EndTime, PersonCharge, PersonMobile, LandlinePhone, JurisdictionRegion, Img, Remark, ViewDel, ViewFlag, ChangeLoginID, ChangeLoginName, ChangeDate, CreateDate from  Organization_Enterprise where ViewDel=0 ";
            }
            else
            {
                sql = "select ID, Code, EnterpriseName, OrganizationForm, OrganizationId, SuperiorsOrganization, ProvinceId, City, Region, Address, StartTime, EndTime, PersonCharge, PersonMobile, LandlinePhone, JurisdictionRegion, Img, Remark, ViewDel, ViewFlag, ChangeLoginID, ChangeLoginName, ChangeDate, CreateDate from  Organization_Enterprise where ViewDel=0 and ID=" + EnterpriseId;
            }
            return _baseRepository.ExecuteSqlCollection<Organization_Enterprise>(sql).ToList();
        }
        public List<AntiFleeing_SalesArea> GetArealistService(int EnterpriseId)
        {
            string sql = "select ID, Name from AntiFleeing_SalesArea where ViewDel=0 and  EnterpriseId=" + EnterpriseId;
            IEnumerable<AntiFleeing_SalesArea> _query = _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql);
            return _query.ToList();
        }
        #endregion
    }
}
